﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GroupManager.Resource.CSDL;
namespace GroupManager.Class
{
    public class LopBan
    {
        // Kiểm tra ban có tồn tại;
        public static bool KTBanTT(int maban)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            if (db.Bans.Where(q => q.MaBan == maban).Count() != 0)
                return true;
            return false;
        }

        // Thêm ban;
        public static void ThemBan(int maban, string tenban, int madnh)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            Ban b = new Ban();
            b.MaBan = maban;
            b.TenBan = tenban;
            b.MaDoiNhom = madnh;
            db.Bans.InsertOnSubmit(b);
            db.SubmitChanges();
        }

        // Sửa ban;
        public static void SuaBan(int maban, string tenban)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var ban = db.Bans.FirstOrDefault(q => q.MaBan == maban);
            ban.TenBan=tenban;
            db.SubmitChanges();
        }

        // Xóa ban;
        public static void XoaBan(int maban)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            Ban b = db.Bans.FirstOrDefault(q => q.MaBan == maban);
            db.Bans.DeleteOnSubmit(b);
            db.SubmitChanges();
        }

        // Lấy thông tin ban theo mã ban;
        public static Ban LayBan(int maban)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = db.Bans.First(q => q.MaBan == maban);
            return sql;
        }

        // Lấy danh sách ban theo mã đội nhóm
        public static IQueryable<Resource.CSDL.Ban> DanhSachBanTheoMaDoi(int madnh)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = db.Bans.Where(q=>q.MaDoiNhom==madnh);
            if (sql.Count() == 0)
                return null;
            return sql;
        }

        // Lấy danh sách ban mà thành viên chưa tham gia theo mã sinh viên;
        public static IQueryable DanhSachBanChuaThamGia(string masv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = db.ThanhVien_Bans.Where(q => q.MaSV == masv);
            var ds = from b in db.Bans
                     join s in sql on b.MaBan equals s.MaBan into c
                     from d in c.DefaultIfEmpty() where d.MaSV==null
                     select new { b.MaBan, b.TenBan};
            return ds;
        }
 
    }
}